#!/usr/bin/python2
#-*- coding=utf-8 -*-

import os
import json
import time
import sys
import argparse
from SqlManager import SqlManager
from collections import defaultdict
from my_api import *

import sys
reload(sys)
sys.setdefaultencoding('utf8')

CONFIG_FILE = 'oss.yaml'

SEC_PER_DAY = 24 * 60 * 60

def total_data(date):
    total_sql = '''
select t.totalRegister, c.currentRegister, r.currentLogin from
    (select count(uid) totalRegister from PlayerRegister where AccountType != 4 and EventTime <= '{0} 23:59:59') t,
    (select count(uid) currentRegister from PlayerRegister where AccountType != 4 and EventTime regexp '^{0}') c,
    (select count(distinct uid) currentLogin from PlayerLogin where AccountType != 4 and EventTime regexp '^{0}') r'''.format(date)

    data = {}
    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = total_sql)

    if not result:
        return data

    data[date] = {'总注册'.decode('utf-8'): (result[0][0] and [result[0][0]] or [0])[0],
                  '当天总注册'.decode('utf-8'): (result[0][1] and [result[0][1]] or [0])[0],
                  '当天总登入'.decode('utf-8'): (result[0][2] and [result[0][2]] or [0])[0]}

    return data

def register_count(condition):
    '''
    and EventTime regexp '^2018-01-16'
    '''

    sql = '''select count(uid) curReg from PlayerRegister where AccountType != 4 ''' + condition
    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = sql)

    if not result:
        return 0

    return (result[0][0] and [result[0][0]] or [0])[0]


def player_online_data():
    sql = '''
select t.GameSvrId, group_concat(t.TotalOnlineCount order by EventTime desc) from (
    select *from PlayerOnline order by EventTime desc limit 10
)as t group by t.GameSvrId'''

    data = {}
    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = sql)

    if not result and isinstance(result, tuple):
        return data

    for k, v in enumerate(result):
        key = (v[0] and [v[0]] or ['unknow' + k])[0]
        data[key] = (v[1] and [v[1]] or [0])[0].split(',')[0]

    return data

def player_online_by_condition(condition):
    sql = '''
select t.GameSvrId, group_concat(t.TotalOnlineCount order by EventTime) from(
    select *from PlayerOnline {} order by EventTime limit 10
)as t group by t.GameSvrId'''.format(condition)

    data = {}
    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = sql)

    if not result and isinstance(result, tuple):
        return data

    for k, v in enumerate(result):
        key = (v[0] and [v[0]] or ['unknow' + k])[0]
        data[key] = (v[1] and [v[1]] or [0])[0].split(',')[0]

    return data

def first_order_login(dates):
    '''一阶登入比'''
    sql = '''select coalesce(xx.ucount, 'total') ucount, count(*)  from (select uid, count(*) ucount from ('''

    for val in dates[1:]:
        sql += ''' select distinct(uid), if(uid, '{0}', null) date from PlayerLogin where EventTime regexp '^{0}' and uid in
            (select uid from PlayerRegister where AccountType != 4 and EventTime regexp '^{1}')''' .format(val, dates[0])

        sql += ''' union'''
    sql = sql[:-5] + ''') as x group by uid) as xx group by xx.ucount with rollup'''

    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = sql)

    #print result, result[-1][0] == 'total', result[0][0] == '1'
    if result and result[-1][0] == 'total':
        if result[0][0] == '1':
            return round(100.0 - result[0][1] * 100.0 / result[-1][1], 1)
        else:
            return 100.0

    return 0.0

def second_order_login(dates):
    '''二阶登入比'''
    sql_mgr = SqlManager()
    drop_tmp = '''drop table if exists tmp'''
    result = sql_mgr.executeSql(sql = drop_tmp)
    if not result:
        return 0;

    create_tmp = '''
create table tmp(uid bigint unsigned) select xx.uid from(
    select x.uid, count(*) ucount from (
    '''

    first_dates = dates[1:7]
    for val in first_dates:
        create_tmp += '''
        select distinct(uid), if(uid, '{0}', null) date from PlayerLogin where EventTime regexp '^{0}' and uid in(select uid from PlayerRegister where AccountType != 4 and EventTime regexp '^{1}') union'''.format(val, dates[0])

    create_tmp = create_tmp[:-5] + ''') as x group by x.uid) as xx where xx.ucount >= 2'''
    result = sql_mgr.executeSql(sql = create_tmp)
    if not result:
        result = sql_mgr.executeSql(sql = drop_tmp)
        return 0;

    query_sql = '''
select x.useful / t.total from (select count(uid) total from tmp) as t, (
    select count(distinct l.uid) useful from (
    '''

    second_dates = dates[-7:]
    for val in second_dates:
        query_sql += '''
        select distinct(uid) from PlayerLogin where EventTime regexp '^{0}' and uid in (select uid from tmp) union'''.format(val)

    query_sql = query_sql[:-5] + ''')as l ) as x;'''

    rate = 0.0
    result = sql_mgr.queryTable(sql = query_sql)
    if result and result[0]:
        rate = round((result[0][0] and [result[0][0]] or [0])[0] * 100, 1)

    sql_mgr.executeSql(sql = drop_tmp)

    return rate

def player_retetion(dates):
    sql = '''
select h.retention , h.retention / r.total from (
    select count(uid) total from PlayerRegister where AccountType != 4 and EventTime regexp '^{0}'
)as r, (
    select count(distinct uid) retention from PlayerLogin where EventTime regexp '^{1}' and uid in(
        select uid from PlayerRegister where AccountType != 4 and EventTime regexp '^{0}')
)as h
    '''

    data = defaultdict(dict)
    sql_mgr = SqlManager()

    #for k, v in enumerate(reversed(dates[:-1]), 1):
    for val in dates[:-1]:
        result = sql_mgr.queryTable(sql = sql.format(val, dates[-1]))
        if not result:
            data[val] = '0 ( 0 )'

        else:
            retention = (result[0][0] and [result[0][0]] or [0])[0]
            rate = round((result[0][1] and [result[0][1]] or [0])[0] * 100, 1)
            data[val][dates[-1]] = '{0} ({1} %)'.format(retention, rate)

    return data

def player_guide_pass_rate(date):
    sql = '''
select p0.lose, p1.step1, p2.step2, p.pass, t.total, p.pass / t.total rate from(
    select count(distinct uid) pass from PlayerGuide where step >= 3 and EventTime < '2018-02-01 23:59:59'
)as p, (
    select count(distinct(uid)) step2 from PlayerGuide where step = '2' and EventTime < '2018-02-01 23:59:59' and uid not in(
        select distinct(uid) from PlayerGuide where step >= 3 and EventTime < '2018-02-01 23:59:59')
)as p2, (
    select count(distinct uid) step1 from PlayerGuide where step = 1 and EventTime < '2018-02-01 23:59:59' and uid not in(
        select distinct(uid) from PlayerGuide where step >= 2 and EventTime < '2018-02-01 23:59:59')
)as p1, (
    select count(distinct uid) lose from PlayerRegister where AccountType != 4 and EventTime < '2018-02-01 23:59:59' and uid not in(
        select distinct(uid) from PlayerGuide where EventTime < '2018-02-01 23:59:59')
)as p0, (
    select count(uid) total from PlayerRegister where AccountType != 4 and EventTime < '2018-02-01 23:59:59'
)as t;
    '''

def query_oss_data(timestamp, file_name):

    timestamps = [val for val in xrange(timestamp - 13 * SEC_PER_DAY, timestamp, SEC_PER_DAY)]
    timestamps.append(timestamp)
    dates = []
    for val in timestamps:
        dates.append(time.strftime("%Y-%m-%d", time.localtime(val)))

    data = defaultdict(dict)

    if os.path.exists(file_name):
        with open(file_name, 'r') as f:
            data.update(json.load(f, encoding='utf-8'))

#     if data.has_key(dates[-1]):
        # return

    data.update(total_data(dates[-1]))
    retentions = player_retetion(dates[-7:])

    for k,v in retentions.iteritems():
        data.has_key(k) and data[k].update(v)

    if data.has_key(dates[-7]):
        data[dates[-7]].update({'一阶登入比'.decode('utf-8'): '{0} %'.format(first_order_login(dates[-7:]))})

    if data.has_key(dates[0]):
        data[dates[0]].update({'二阶登入比'.decode('utf-8'): '{0} %'.format(second_order_login(dates))})

    print(json.dumps(data, indent=4, encoding='utf-8', ensure_ascii=False))

    with open(file_name, 'w') as f:
        json.dump(data, f, indent=4, encoding='utf-8', ensure_ascii=False, sort_keys=True);


def argument_parser():
    parser = argparse.ArgumentParser()
    parser.add_argument('-m', '--mail', help='wether to send mail or not',  action='store_true')
    parser.add_argument('-s', '--subject', help='the subject of mail',  default='taptap')

    parser.add_argument('-f', '--file', help='the path about statistics file',  default='statistics.txt')

    parser.add_argument('-H', '--host', help='mysql host',  default='localhost')
    parser.add_argument('-P', '--PORT', help='mysql port',  default='3306')
    parser.add_argument('-u', '--user', help='mysql user\'s name',  default='root')
    parser.add_argument('-p', '--password', help='mysql user\'s passwrod', required=True)
    parser.add_argument('-D', '--database', help='database name',  default='oss')
    parser.add_argument('-c', '--character', help='the setting about mysql database\'s character', default='utf8')

    group = parser.add_mutually_exclusive_group(required=True)
    group.add_argument('-d', '--days', help='collect the statistics information from which day to yesterday', type=int)
    group.add_argument('-t', '--timestamp', help='collect the statistics information about the day of the timestamp', type=int)
    group.add_argument('-y', '--yesterday', help='collect the yesterday\'s statistics information', action='store_true')

    return parser.parse_args()

def init_functions():
    return {
                'statistics': query_oss_data,
                'online': player_online_data
           }


def caculate(start, end = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))):
    sql_mgr = SqlManager()
    data = defaultdict()
    text = []
    text.append(start)

    condition = """ EventTime >= '{0}' and EventTime < '{1}' """.format(start, end)

    sql_register = """select count(uid) from PlayerRegister where AccountType != 4 and """ + condition
    result = sql_mgr.queryTable(sql = sql_register)
    if not result:
        data['register'] = 'error'
    else:
        data['register'] = (result[0][0] and [result[0][0]] or [0])[0]

    text.append(str(data['register']))

    sql_login = """select count(distinct uid) from PlayerLogin where AccountType != 4 and """ + condition
    result = sql_mgr.queryTable(sql = sql_login)
    if not result:
        data['login'] = 'error'
    else:
        data['login'] = (result[0][0] and [result[0][0]] or [0])[0]

    text.append(str(data['login']))

    sql_battle = """select count(*) from PlayerEndGame where uid in (select uid from PlayerRegister where AccountType != 4 and {0}) """.format(condition)
    result = sql_mgr.queryTable(sql = sql_battle)
    if not result:
        data['battle'] = 'error'
    else:
        data['battle'] = (result[0][0] and [result[0][0]] or [0])[0]

    text.append(str(data['battle']))

    timestamp = int(time.mktime(time.strptime(start, "%Y-%m-%d %H:%M:%S")))
    tomorrow = time.strftime("%Y-%m-%d", time.localtime(timestamp + SEC_PER_DAY))
    sql_retention = """
select h.retention , h.retention / r.total from (
   select count(uid) total from PlayerRegister where AccountType != 4 and {0}
)as r, (
   select count(distinct uid) retention from PlayerLogin where EventTime regexp '{1}' and uid in(
      select uid from PlayerRegister where AccountType != 4 and {0})
)as h""".format(condition, tomorrow)
    result = sql_mgr.queryTable(sql = sql_retention)
    if not result:
        data['rate'] = 'error'
    else:
        count = (result[0][0] and [result[0][0]] or [0])[0]
        rate = (result[0][1] and [result[0][1]] or [0])[0]
        data['rate'] = '{0}'.format(count)

    text.append(str(data['rate']))

    sql_guide = """
select p0.step0, p1.step1, p2.step2, p3.step3, p4.step4 from(
   select count(distinct uid) step0 from PlayerGuide where AccountType != 4 and uid in (select uid from PlayerRegister where AccountType != 4 and {0})

)as p0, (
   select count(distinct uid) step1 from PlayerGuide where AccountType != 4 and step = 1 and uid in (select uid from PlayerRegister where AccountType != 4 and {0})

)as p1, (
   select count(distinct(uid)) step2 from PlayerGuide where AccountType != 4 and step = 2 and uid in (select uid from PlayerRegister where AccountType != 4 and {0})

)as p2, (
   select count(distinct uid) step3 from PlayerGuide where AccountType != 4 and step = 3 and uid in (select uid from PlayerRegister where AccountType != 4 and {0})

)as p3, (
   select count(distinct uid) step4 from PlayerGuide where AccountType != 4 and step = 4 and uid in (select uid from PlayerRegister where AccountType != 4 and {0})

)as p4""".format(condition)
    result = sql_mgr.queryTable(sql = sql_guide)
    if not result:
        data['guide'] = 'error'
        text.append('error|' * 4 + 'error')
    else:
        data['guide'] = {
                         'step0': (result[0][0] and [result[0][0]] or [0])[0],
                         'step1': (result[0][1] and [result[0][1]] or [0])[0],
                         'step2': (result[0][2] and [result[0][2]] or [0])[0],
                         'step3': (result[0][3] and [result[0][3]] or [0])[0],
                         'step4': (result[0][4] and [result[0][4]] or [0])[0]}

        text.append(str(data['guide']['step0']))
        text.append(str(data['guide']['step1']))
        text.append(str(data['guide']['step2']))
        text.append(str(data['guide']['step3']))
        text.append(str(data['guide']['step4']))


    sql_online = """
select t.GameSvrId, group_concat(t.TotalOnlineCount order by EventTime desc) from (
   select *from PlayerOnline where {0} order by EventTime
)as t group by t.GameSvrId""".format(condition)
    result = sql_mgr.queryTable(sql = sql_online)
    if not result:
        data['online'] = 'error'
        text.append('error')
        return data

    onlines = []
    for k, v in enumerate(result):
        tmp = (v[1] and [v[1]] or [''])[0].split(',')
        tmp = map(lambda val: int(val), tmp)
        onlines.append(round(sum(tmp) * 1.0  / len(tmp), 1))

    data['online'] = sum(onlines)
    text.append(str(data['online']))

    return data, '|'.join(text)



def abnormal(date):
    timestamp = int(time.mktime(time.strptime(date, "%Y-%m-%d %H:%M:%S")))
    now = int(time.time())
    interval = 60 * 60
    timestamps = [val for val in xrange(timestamp, now, interval)]
    #timestamps.append(timestamps[-1] + interval)
    dates = map(lambda val: time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(val)), timestamps)

    texts = '时间|注册|登入|战斗场次|次留|step0|step1|step2|step3|step4|平均在线\n'
    data = defaultdict()
    for k, v in enumerate(dates, 1):
        try:
            if k == len(dates):
                #data[v], text  = caculate(v)
                data, text  = caculate(v)
                texts += text + '\n'
            else:
                #data[v], text = caculate(v, dates[k])
                    data, text = caculate(v, dates[k])
                    texts += text + '\n'
        except Exception as e:
            print e

    #print(json.dumps(data, indent=4, encoding='utf-8', ensure_ascii=False, sort_keys=True))
    with open('statistics.csv', 'w') as f:
        f.write(texts)


def main():
    args = argument_parser();
    sql_mgr = SqlManager()
    sql_mgr.setConnect(args.database, args.host, args.user, args.password, args.character)

    config = get_config(os.path.join(os.path.dirname(__file__), CONFIG_FILE))
    setting = config.get('setting', {})

    timestamp = (args.timestamp and [args.timestamp] or [time.time() - 1800])[0]
    date = time.strftime("%Y-%m-%d", time.localtime(timestamp))

    count = (args.days and [args.days] or [1])[0]

    sys_fun = init_functions()

    mail_files = []

    content = []

    data = total_data(date)
    for k, v in data[date].iteritems():
        content.append("\n {0}: {1}".format(k.encode('utf-8'), v))
    
    hours = [val for val in xrange(0, 24)]
    hours = map(lambda val: '%02d' %val, hours)
    for hour in hours:
        content.append("\n\n{0} {1}时".format(date, hour))
        data = player_online_by_condition(''' where EventTime regexp '^{0} {1}' '''.format(date, hour))
        for k, v in data.iteritems():
            content.append('\n        服务器 ID: {0}   在线人数: {1}'.format(k, v))
    

    if args.mail:
        mail = setting.get('mail', {})
        subject = mail.get('subject', '{} {}')

        send_mail(mail, '{} taptap统计(刘铁铮专属)'.format(date), ''.join(content), mail_files, False)


if __name__ == "__main__":
    main()
